﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using VehicleConfigurator.NetUtils.Database;
using System.Collections;
using VehicleConfigurator.FormDetails;
using VehicleConfigurator.NetUtils.Model;
using VehicleConfigurator.Database;
using VehicleConfigurator.NetUtils;
using VehicleConfigurator.NetUtils.Settings;
using VehicleConfigurator.Model;
using System.IO;
using System.Globalization;


namespace VehicleConfigurator.TabControls
{
    public partial class ModelPriceListControl : UserControl
    {
        public ModelPriceListControl()
        {
            InitializeComponent();
        }

        private object _1INSTANCE = null;
        private string tax = "";

        public void GetVatFromCountry(string countryoid)
        {
            tax = DBAccess.GetTaxFromCountry(countryoid);

        }

        public void LoadDataComponent()
        {
            //try
            //{
                if (_1INSTANCE == null)
                {
                    _1INSTANCE = this;
                }
                else
                {
                    return;
                }
                // Data
                //var pricelist = DBAccess.GetModelPricelist();
                var optionPricelistDate = DBAccess.GetModelPricelistDate();
                var makeData = DBAccess.GetMakes();
                var countryData = DBAccess.GetCountry();
                var currencyData = DBAccess.GetCurrency();
                // Make
                this.cboMakes.DataSource = makeData;
                this.cboMakes.ValueMember = FieldNames.OID;
                this.cboMakes.DisplayMember = FieldNames.NAME;
                // Country
                this.cboCountry.DataSource = countryData;
                this.cboCountry.ValueMember = FieldNames.OID;
                this.cboCountry.DisplayMember = FieldNames.NAME;
                Utils.SetDefault(countryData, this.cboCountry);
                // Currency
                //DataTable dtCur = currencyData;
                this.cboCurrency.DataSource = currencyData;
                this.cboCurrency.ValueMember = FieldNames.OID;
                this.cboCurrency.DisplayMember = FieldNames.NAME;
                Utils.SetDefault(currencyData, this.cboCurrency);
                // PriceListDate
                var dateOnly = optionPricelistDate.AsEnumerable().Select(row => row[FieldNames.PRICE_LIST_DATE]).Distinct().ToList();
                if (!dateOnly.Contains(DateTime.Today))
                {
                    dateOnly.Insert(0, DateTime.Today);
                }
                dateOnly.Sort();
                dateOnly.Reverse();
                this.cboPricelistDateOptionPricelist.DataSource = dateOnly;
                this.cboPricelistDateOptionPricelist.FormatString = AppConfig.Constants.DEFAULT_DATE_FORMAT;
                // Language
                DataTable dt = (DataTable)DBAccess.GetLanguage();
                this.cbLanguageOptionPricelist.DataSource = dt; // DBAccess.GetLanguage();
                this.cbLanguageOptionPricelist.ValueMember = FieldNames.OID;
                this.cbLanguageOptionPricelist.DisplayMember = FieldNames.NAME;

                Utils.SetDefault(dt, this.cbLanguageOptionPricelist);


                // Load datagridview
                reloadDataGridView();

                //btnSearchOptionPricelist_Click(this, null);

                // Set datasource gridview
                var comboBox = this.gvModelPricelist.Columns["OptionGroup"] as DataGridViewComboBoxColumn;
                //var ds = (DataTable)this.gvOptionPricelist.DataSource;
                //comboBox.DataSource = DBAccess.GetOptionGroup();
                //comboBox.ValueMember = FieldNames.OID;
                //comboBox.DisplayMember = FieldNames.NAME;
                this.btnSearchOptionPricelist_Click(this, null);

            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //}
        }

        private void Reorder()
        {
            var tmpIndex = 0;
            this.gvModelPricelist.Columns[FieldNames.OID].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns[FieldNames.OID_COUNTRY].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns[FieldNames.OID_CURRENCY].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns[FieldNames.OID_SUBMODEL].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns[FieldNames.OID_MODEL].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns[FieldNames.OID_MODEL_EDITION].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns[FieldNames.OID_MODEL_YEAR].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns[FieldNames.MODEL_CODE].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns[FieldNames.SUBMODEL_NAME].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["CylinderCapacity"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["EngineType"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["PowerKW"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["GearBoxType"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["CO2EmissionMin"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["CO2EmissionMax"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["CO2EmissionAvg"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["EmissionNorm"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["FuelType"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["DealerNetPrice"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["DealerNetPriceVAT"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["RetailPrice"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["RetailPriceVAT"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["TaxAmount1"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["TaxAmount2"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["TaxAmount3"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["TaxPercentage1"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["TaxPercentage2"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["TaxPercentage3"].DisplayIndex = tmpIndex++;
            this.gvModelPricelist.Columns["PriceListDate"].DisplayIndex = tmpIndex++;
        }


        private void searchModelPricelist()
        {
            // Remove Data (Binding)
            //this.gvModelPricelist.DataSource = null;
            //var pricelist = DBAccess.GetModelPricelist();
            //reloadDataGridView(pricelist);
            //btnSearchOptionPricelist_Click(this, null);
            // Make OID
            var dtRow = this.cboMakes.SelectedItem as DataRowView;
            var makeOid = dtRow == null ? string.Empty : dtRow[FieldNames.OID].ToString();
            // Model OID
            var modelRow = this.cboModel.SelectedItem as DataRowView;
            var modelOid = modelRow == null ? string.Empty : modelRow[FieldNames.OID].ToString();
            // Model Year OID
            dtRow = this.cboModelYear.SelectedItem as DataRowView;
            var modelYearOid = dtRow == null ? string.Empty : dtRow[FieldNames.OID].ToString();
            // Country
            dtRow = this.cboCountry.SelectedItem as DataRowView;
            var countryOid = dtRow == null ? string.Empty : dtRow[FieldNames.OID].ToString();
            // Currency
            dtRow = this.cboCurrency.SelectedItem as DataRowView;
            var currencyOid = dtRow == null ? string.Empty : dtRow[FieldNames.OID].ToString();

            this.GetVatFromCountry(countryOid);

            // Pricelist Date
            //dtRow = this.cboPricelistDateOptionPricelist.SelectedItem as DataRowView;
            //var pricelistDate = dtRow == null ? string.Empty : ((DateTime)dtRow[FieldNames.PriceListDate]).ToString(AppConfig.Constants.DEFAULT_DATE_FORMAT);
            var pricelistDate = string.Empty;
            if (this.cboPricelistDateOptionPricelist.SelectedItem != null)
            {
                pricelistDate = ((DateTime)this.cboPricelistDateOptionPricelist.SelectedItem).ToString(AppConfig.Constants.DEFAULT_DATE_FORMAT);
            }
            // Remove all data
            if (this.gvModelPricelist.DataSource != null)
            {
                (this.gvModelPricelist.DataSource as DataTable).Clear();
            }
            // Add datalist: Model, Edition, Model year
            // Model
            var cboModelNameCol = this.gvModelPricelist.Columns["ModelOID"] as DataGridViewComboBoxColumn;
            cboModelNameCol.DataSource = null;
            if (modelRow != null)
            {
                var dsModel = (this.cboModel.DataSource as DataTable).Clone();
                dsModel.ImportRow(modelRow.Row);
                cboModelNameCol.DataSource = dsModel;
                cboModelNameCol.ValueMember = FieldNames.OID;
                cboModelNameCol.DisplayMember = FieldNames.NAME;
            }
            // Edition
            var dsModelEdition = DBAccess.GetModelEdition(modelOid);
            var cboModelEdition = this.gvModelPricelist.Columns["ModelEditionOID"] as DataGridViewComboBoxColumn;
            cboModelEdition.DataSource = null;
            if (dsModelEdition != null && dsModelEdition.Rows.Count > 0)
            {
                cboModelEdition.DataSource = dsModelEdition;
                cboModelEdition.ValueMember = FieldNames.OID;
                cboModelEdition.DisplayMember = FieldNames.NAME;
            }
            // Model year

            var dsModelYear = this.cboModelYear.DataSource == null ? null : (this.cboModelYear.DataSource as DataTable).Copy();
            var cboModelYear = this.gvModelPricelist.Columns["ModelYearOID"] as DataGridViewComboBoxColumn;
            cboModelYear.DataSource = null;
            if (dsModelYear != null && dsModelYear.Rows.Count > 0)
            {
                cboModelYear.DataSource = dsModelYear;
                cboModelYear.ValueMember = FieldNames.OID;
                cboModelYear.DisplayMember = FieldNames.NAME;
            }
            // SubModel
            //var dsSubModel = DBAccess.GetSubModel(modelOid, string.Empty, modelYearOid);
            //if (dsSubModel != null && dsSubModel.Rows.Count > 0)
            //{
            //    var cbSubModel = this.gvModelPricelist.Columns["SubModelOID"] as DataGridViewComboBoxColumn;
            //    cbSubModel.DataSource = dsSubModel;
            //    cbSubModel.ValueMember = FieldNames.OID;
            //    cbSubModel.DisplayMember = FieldNames.NAME;
            //}
            // Load datagridview
            // Search
            var pricelist = DBAccess.GetModelPricelist(makeOid, modelOid, modelYearOid, countryOid, currencyOid, pricelistDate);
            this.gvModelPricelist.DataSource = pricelist;
            Reorder();
        }

        private void reloadDataGridView()
        {
            searchModelPricelist();
        }

        private void ScrollToBottom()
        {
            var lastRow = this.gvModelPricelist.Rows.Count - 1;
            this.gvModelPricelist.FirstDisplayedScrollingRowIndex = lastRow;
        }

        private SubModelPrice getSubModelPrice(DataRow dataRow)
        {
            if (dataRow != null)
            {
                //var dataRow = this.gvModelPricelist.SelectedRows[0];
                //var makeOid = (int)dataRow.Cells[FieldNames.OID_MAKE].Value;
                var countryOid = (int)dataRow[FieldNames.OID_COUNTRY];
                var currencyOid = (int)dataRow[FieldNames.OID_CURRENCY];
                var modelOid = (int)dataRow[FieldNames.OID_MODEL];
                var modelYearOid = (int)dataRow[FieldNames.OID_MODEL_YEAR];
                //var pricelistDate = DateTime.Today;
                var pricelistDate = cboPricelistDateOptionPricelist.SelectedIndex == -1 ? DateTime.Today : ((DateTime)cboPricelistDateOptionPricelist.SelectedValue);
                pricelistDate = dataRow[FieldNames.PRICE_LIST_DATE] is System.DBNull ? pricelistDate : (DateTime)dataRow[FieldNames.PRICE_LIST_DATE];
                var subModelName = dataRow[FieldNames.NAME] is DBNull ? string.Empty : dataRow[FieldNames.NAME] as string;

                // [[SubModel Data]]
                var modelEditionOid = (int)dataRow[FieldNames.OID_MODEL_EDITION];
                var modelCode = dataRow["ModelCode"] is DBNull ? string.Empty : dataRow["ModelCode"] as string;
                var CylinderCapacity = dataRow["CylinderCapacity"] is DBNull ? 0 : (int)dataRow["CylinderCapacity"];
                var EngineType = dataRow["EngineType"] is DBNull ? string.Empty : dataRow["EngineType"] as string;
                var PowerKW = dataRow["PowerKW"] is DBNull ? 0 : (int)dataRow["PowerKW"];
                var GearBoxType = dataRow["GearBoxType"] is DBNull ? string.Empty : dataRow["GearBoxType"] as string;
                var CO2EmissionMin = dataRow["CO2EmissionMin"] is DBNull ? 0 : (decimal)dataRow["CO2EmissionMin"];
                var CO2EmissionMax = dataRow["CO2EmissionMax"] is DBNull ? 0 : (decimal)dataRow["CO2EmissionMax"];
                var CO2EmissionAvg = dataRow["CO2EmissionAvg"] is DBNull ? 0 : (decimal)dataRow["CO2EmissionAvg"];
                var EmissionNorm = dataRow["EmissionNorm"] is DBNull ? string.Empty : dataRow["EmissionNorm"] as string;
                var FuelType = dataRow["FuelType"] is DBNull ? string.Empty : dataRow["FuelType"] as string;

                // [[SubModelPrice]]
                // TaxPercentage
                var taxPercentage1 = dataRow["TaxPercentage1"] is DBNull ? 0 : (decimal)dataRow["TaxPercentage1"];
                var taxPercentage2 = dataRow["TaxPercentage2"] is DBNull ? 0 : (decimal)dataRow["TaxPercentage2"];
                var taxPercentage3 = dataRow["TaxPercentage3"] is DBNull ? 0 : (decimal)dataRow["TaxPercentage3"];
                // TaxAmount
                var taxAmount1 = dataRow["TaxAmount1"] is DBNull ? 0 : (decimal)dataRow["TaxAmount1"];
                var taxAmount2 = dataRow["TaxAmount2"] is DBNull ? 0 : (decimal)dataRow["TaxAmount2"];
                var taxAmount3 = dataRow["TaxAmount3"] is DBNull ? 0 : (decimal)dataRow["TaxAmount3"];
                // Dealer
                var dealerNetPrice = dataRow["DealerNetPrice"] is DBNull ? 0 : (decimal)dataRow["DealerNetPrice"];
                var dealerNetPriceVAT = dataRow["DealerNetPriceVAT"] is DBNull ? 0 : (decimal)dataRow["DealerNetPriceVAT"];
                // Retail
                var retailPrice = dataRow["RetailPrice"] is DBNull ? 0 : (decimal)dataRow["RetailPrice"];
                var retailPriceVAT = dataRow["RetailPriceVAT"] is DBNull ? 0 : (decimal)dataRow["RetailPriceVAT"];
                var subModelOid = (dataRow["SubModelOID"] is int) ? (int)dataRow["SubModelOID"] : -1;
                var subModelPriceOid = (dataRow[FieldNames.OID] is int) ? (int)dataRow[FieldNames.OID] : -1;
                // Duplicate data
                var subModelPrice = new SubModelPrice();
                // subModel
                subModelPrice.Name = subModelName;
                subModelPrice.ModelCode = modelCode;
                subModelPrice.ModelOid = modelOid;
                subModelPrice.ModelYearOid = modelYearOid;
                subModelPrice.ModelEditionOid = modelEditionOid;
                subModelPrice.CylinderCapacity = CylinderCapacity;
                subModelPrice.EngineType = EngineType;
                subModelPrice.PowerKW = PowerKW;
                subModelPrice.GearBoxType = GearBoxType;
                subModelPrice.GearBoxType = GearBoxType;
                subModelPrice.CO2EmissionMin = CO2EmissionMin;
                subModelPrice.CO2EmissionMax = CO2EmissionMax;
                subModelPrice.CO2EmissionAvg = CO2EmissionAvg;
                subModelPrice.EmissionNorm = EmissionNorm;
                subModelPrice.FuelType = FuelType;

                // subModelPrice
                subModelPrice.OID = subModelPriceOid;
                subModelPrice.SubModelOid = subModelOid;
                subModelPrice.SubModelName = subModelName;
                subModelPrice.PricelistDate = pricelistDate;
                subModelPrice.CountryOid = countryOid;
                subModelPrice.CurrencyOid = currencyOid;
                subModelPrice.TaxAmount1 = taxAmount1;
                subModelPrice.TaxAmount2 = taxAmount2;
                subModelPrice.TaxAmount3 = taxAmount3;
                subModelPrice.TaxPercentage1 = taxPercentage1;
                subModelPrice.TaxPercentage2 = taxPercentage2;
                subModelPrice.TaxPercentage3 = taxPercentage3;
                subModelPrice.DealerNetPrice = dealerNetPrice;
                subModelPrice.DealerNetPriceVAT = dealerNetPriceVAT;
                subModelPrice.RetailPrice = retailPrice;
                subModelPrice.RetailPriceVAT = retailPriceVAT;
                return subModelPrice;
            }
            else
            {
                return null;
            }
        }

        private SubModelPrice getSubModelPrice(DataGridViewRow dataRow)
        {
            if (dataRow != null)
            {
                //var dataRow = this.gvModelPricelist.SelectedRows[0];
                //var makeOid = (int)dataRow.Cells[FieldNames.OID_MAKE].Value;
                var countryOid = (int)dataRow.Cells[FieldNames.OID_COUNTRY].Value;
                var currencyOid = (int)dataRow.Cells[FieldNames.OID_CURRENCY].Value;
                var modelYearOid = (int)dataRow.Cells[FieldNames.OID_MODEL_YEAR].Value;
                var pricelistDate = (DateTime)dataRow.Cells[FieldNames.PRICE_LIST_DATE].Value;
                var subModelName = dataRow.Cells[FieldNames.SUBMODEL_NAME].Value as string;
                // TaxPercentage
                var taxPercentage1 = (decimal)dataRow.Cells["TaxPercentage1"].Value;
                var taxPercentage2 = (decimal)dataRow.Cells["TaxPercentage2"].Value;
                var taxPercentage3 = (decimal)dataRow.Cells["TaxPercentage3"].Value;
                // TaxAmount
                var taxAmount1 = (decimal)dataRow.Cells["TaxAmount1"].Value;
                var taxAmount2 = (decimal)dataRow.Cells["TaxAmount2"].Value;
                var taxAmount3 = (decimal)dataRow.Cells["TaxAmount3"].Value;
                // Dealer
                var dealerNetPrice = (decimal)dataRow.Cells["DealerNetPrice"].Value;
                var dealerNetPriceVAT = (decimal)dataRow.Cells["DealerNetPriceVAT"].Value;
                // Retail
                var retailPrice = (decimal)dataRow.Cells["RetailPrice"].Value;
                var retailPriceVAT = (decimal)dataRow.Cells["RetailPriceVAT"].Value;
                var subModelOid = (int)dataRow.Cells["SubModelOID"].Value;
                // Duplicate data
                var subModelPrice = new SubModelPrice();
                subModelPrice.SubModelOid = subModelOid;
                subModelPrice.SubModelName = subModelName;
                subModelPrice.PricelistDate = pricelistDate;
                subModelPrice.CountryOid = countryOid;
                subModelPrice.CurrencyOid = currencyOid;
                subModelPrice.TaxAmount1 = taxAmount1;
                subModelPrice.TaxAmount2 = taxAmount2;
                subModelPrice.TaxAmount3 = taxAmount3;
                subModelPrice.TaxPercentage1 = taxPercentage1;
                subModelPrice.TaxPercentage2 = taxPercentage2;
                subModelPrice.TaxPercentage3 = taxPercentage3;
                subModelPrice.DealerNetPrice = dealerNetPrice;
                subModelPrice.DealerNetPriceVAT = dealerNetPriceVAT;
                subModelPrice.RetailPrice = retailPrice;
                subModelPrice.RetailPriceVAT = retailPriceVAT;
                return subModelPrice;
            }
            else
            {
                return null;
            }
        }

        private void cboMakes_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                var dtRow = this.cboMakes.SelectedItem as DataRowView;
                var makeOid = dtRow[FieldNames.OID].ToString();
                // Model
                this.cboModel.DataSource = DBAccess.GetModels(makeOid);
                this.cboModel.ValueMember = FieldNames.OID;
                this.cboModel.DisplayMember = FieldNames.NAME;
                // Model year
                this.cboModelYear.DataSource = DBAccess.GetModelYear(makeOid);
                this.cboModelYear.ValueMember = FieldNames.OID;
                this.cboModelYear.DisplayMember = FieldNames.NAME;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnSearchOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                this.searchModelPricelist();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnDuplicateOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                if (this.gvModelPricelist.Rows.Count > 0)
                {
                    // Get duplicated data
                    //var makeOid = (this.cboMakes.SelectedItem as DataRowView)[FieldNames.OID].ToString();
                    //var modelOid = (int)(this.cboModel.SelectedItem as DataRowView)[FieldNames.OID];
                    var modelYearOid = (int)(this.cboModelYear.SelectedItem as DataRowView)[FieldNames.OID];
                    var countryOid = (int)(this.cboCountry.SelectedItem as DataRowView)[FieldNames.OID];
                    var currencyOid = (int)(this.cboCurrency.SelectedItem as DataRowView)[FieldNames.OID];
                    var pricelistDate = cboPricelistDateOptionPricelist.SelectedIndex == -1 ? DateTime.Today : ((DateTime)cboPricelistDateOptionPricelist.SelectedValue);
                    // Clone
                    var make = this.cboMakes.DataSource as DataTable;
                    var country = this.cboCountry.DataSource as DataTable;
                    var currency = this.cboCurrency.DataSource as DataTable;
                    var modelYear = this.cboModelYear.DataSource as DataTable;
                    // Init form
                    var duplicateForm = new FrmDuplicatePricelist();
                    duplicateForm.LoadDataComponent(country.Copy(), currency.Copy(), modelYear.Copy());
                    duplicateForm.LoadDataDefault(countryOid, currencyOid, modelYearOid, pricelistDate);
                    duplicateForm.FormClosed += new FormClosedEventHandler(duplicateForm_FormClosed);
                    duplicateForm.ShowDialog();
                    //this.reloadDataGridView();
                }
                else
                {
                    MessageBox.Show("Please select a full row to duplicate");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void duplicateForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            try
            {
                var form = sender as Form;
                if (form.DialogResult == DialogResult.OK)
                {
                    var data = form.Tag as Hashtable;
                    // parse
                    var dataRow = data[FieldNames.OID] as DataGridViewRow;
                    var countryOid = (int)data[FieldNames.OID_COUNTRY];
                    var currencyOid = (int)data[FieldNames.OID_CURRENCY];
                    var modelYearOid = (int)data[FieldNames.OID_MODEL_YEAR];
                    var pricelistDate = (DateTime)data[FieldNames.PRICE_LIST_DATE];

                    // Update data
                    var dt = this.gvModelPricelist.DataSource as DataTable;
                    var duplicatedDs = dt.Clone();
                    foreach (var item in dt.Rows)
                    {
                        var sourceRow = item as DataRow;
                        var desRow = duplicatedDs.NewRow();
                        desRow.ItemArray = sourceRow.ItemArray.Clone() as object[];
                        desRow[FieldNames.OID] = "-1";
                        desRow[FieldNames.OID_COUNTRY] = countryOid;
                        desRow[FieldNames.OID_CURRENCY] = currencyOid;
                        desRow[FieldNames.OID_MODEL_YEAR] = modelYearOid;
                        desRow[FieldNames.PRICE_LIST_DATE] = pricelistDate;
                        // add new
                        duplicatedDs.Rows.Add(desRow);
                    }
                    // Save to dabase
                    //DBAccess.CreateSubModelPrice(duplicatedSubModelPrice);
                    // update view
                    dt.Clear();
                    this.gvModelPricelist.DataSource = duplicatedDs;
                    this.ScrollToBottom();
                    // Update conditions
                    this.cboModelYear.SelectedValue = modelYearOid;
                    this.cboCountry.SelectedValue = countryOid;
                    this.cboCurrency.SelectedValue = currencyOid;
                    //this.cboOptionGroupPricelist.SelectedValue = groupOid;
                    var dateOnly = this.cboPricelistDateOptionPricelist.DataSource as List<object>;
                    if (dateOnly != null && !dateOnly.Contains(pricelistDate))
                    {
                        dateOnly.Add(pricelistDate);
                        dateOnly.Sort();
                        dateOnly.Reverse();
                        this.cboPricelistDateOptionPricelist.DataSource = null;
                        this.cboPricelistDateOptionPricelist.DataSource = dateOnly;
                    }

                    var selectIndex = dateOnly.IndexOf(pricelistDate);
                    if (selectIndex != -1)
                    {
                        this.cboPricelistDateOptionPricelist.SelectedIndex = selectIndex;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void btnDuplicateSelectedOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                if (this.gvModelPricelist.SelectedRows.Count > 0)
                {
                    // Duplicate data
                    var dataRow = this.gvModelPricelist.SelectedRows[0];
                    var subModelPrice = getSubModelPrice(dataRow);
                    // Confirm ?
                    //var message = string.Format("Are you sure to duplicate pricelist: {0}", subModelPrice.SubModelName);
                    //var userConfirm = MessageBox.Show(message, "Confirm ?", MessageBoxButtons.YesNo);
                    //// Init form
                    //if (userConfirm == DialogResult.Yes)
                    //{
                    // Save to dabase
                    //DBAccess.CreateSubModelPrice(subModelPrice);
                    //this.reloadDataGridView();
                    var dt = this.gvModelPricelist.DataSource as DataTable;
                    int index = dataRow.Index;
                    var desRow = dt.NewRow();
                    var sourceRow = dt.Rows[index];
                    desRow.ItemArray = sourceRow.ItemArray.Clone() as object[];
                    desRow[FieldNames.OID] = "-1";
                    dt.Rows.Add(desRow);
                    this.ScrollToBottom();
                    //}
                }
                else
                {
                    MessageBox.Show("Please select a full row to duplicate");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void btnSaveOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                var ds = this.gvModelPricelist.DataSource as DataTable;
                //clsDb db = new clsDb();
                //db.updateData("submodelprice", this.gvModelPricelist);
                var added = ds.GetChanges(DataRowState.Added);
                if (added != null && added.Rows.Count > 0)
                {
                    //var subModelTb = Utils.CopyColumns(added, "");
                    var countSuccess = 0;
                    foreach (var item in added.Rows)
                    {
                        var dataRow = item as DataRow;
                        var subModelPrice = getSubModelPrice(dataRow);
                        var result = DBAccess.CreateNewSubModelPrice(subModelPrice);
                        countSuccess++;
                    }
                    //MessageBox.Show(string.Format("Create successful: {0} item(s)", countSuccess));
                }
                var changes = ds.GetChanges(DataRowState.Modified);
                if (changes != null && changes.Rows.Count > 0)
                {
                    var countSuccess = 0;
                    foreach (var item in changes.Rows)
                    {
                        var dataRow = item as DataRow;
                        var subModelPrice = getSubModelPrice(dataRow);
                        var result = DBAccess.UpdateSubModelPrice(subModelPrice);
                        countSuccess++;
                    }
                    //MessageBox.Show(string.Format("Update successful: {0} item(s)", countSuccess));
                }
                //DBAccess.UpdateDataSource(ds.Copy(), "submodel", "submodelprice");
                //this.reloadDataGridView();
                var deleted = ds.GetChanges(DataRowState.Deleted);
                var ids1 = Utils.getIdDelete(deleted, FieldNames.OID);
                DBAccess.DeleteByOid(FieldNames.Table.SUBMODEL_PRICE, ids1);
                ds.AcceptChanges();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Please check input: " + ex.Message);
            }
        }

        private void btnDeleteSelectedOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                foreach (DataGridViewRow item in this.gvModelPricelist.SelectedRows)
                {
                    this.gvModelPricelist.Rows.RemoveAt(item.Index);
                }
                //if (this.gvModelPricelist.SelectedRows.Count > 0)
                //{
                //    // Get duplicated data
                //    var dataRow = this.gvModelPricelist.SelectedRows[0];
                //    if (!(dataRow.Cells[FieldNames.OID].Value is int))
                //    {
                //        var ds = this.gvModelPricelist.DataSource as DataTable;
                //        ds.Rows.RemoveAt(dataRow.Index);
                //        return;
                //    }
                //    var subModelPriceOid = (int)dataRow.Cells[FieldNames.OID].Value;
                //    //var subModelOid = (int)dataRow.Cells["SubModelOid"].Value; ? delete ?
                //    var subModelName = dataRow.Cells[FieldNames.NAME_SUBMODEL].Value as string;
                //    // Confirm ?
                //    var message = string.Format("Are you sure to delete pricelist: {0}", subModelName);
                //    var userConfirm = MessageBox.Show(message, "Delete confirm ?", MessageBoxButtons.YesNo);
                //    // Init form
                //    if (userConfirm == DialogResult.Yes)
                //    {
                //        DBAccess.DeleteByOid("submodelprice", subModelPriceOid);
                //        this.reloadDataGridView();
                //    }
                //}
                //else
                //{
                //    MessageBox.Show("Please select a full row to delete");
                //}
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnNewOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                //var index = this.gvModelPricelist.Rows.Add();
                var ds = this.gvModelPricelist.DataSource as DataTable;
                var dsModel = (this.gvModelPricelist.Columns["ModelOID"] as DataGridViewComboBoxColumn).DataSource as DataTable;
                var cboModelEdition = (this.gvModelPricelist.Columns["ModelEditionOID"] as DataGridViewComboBoxColumn);

                // Default value
                var cboMakeRow = this.cboMakes.SelectedItem as DataRowView;
                var makeOid = cboMakeRow[FieldNames.OID].ToString();
                var modelOid = (this.cboModel.SelectedItem == null) ? -1 : (int)(this.cboModel.SelectedItem as DataRowView)[FieldNames.OID];
                var modelYearOid = (this.cboModelYear.SelectedItem == null) ? -1 : (int)(this.cboModelYear.SelectedItem as DataRowView)[FieldNames.OID];
                var countryOid = this.cboCountry.SelectedItem == null ? -1 : (int)(this.cboCountry.SelectedItem as DataRowView)[FieldNames.OID];
                var currencyOid = this.cboCurrency.SelectedItem == null ? -1 : (int)(this.cboCurrency.SelectedItem as DataRowView)[FieldNames.OID];

                var row = ds.NewRow();

                row["CountryOID"] = countryOid;
                row["CurrencyOID"] = currencyOid;
                row["ModelOID"] = modelOid;
                row["ModelYearOID"] = modelYearOid;
                //row["PriceListDate"] = 
                //row["ModelEditionOID"] = cboModelEdition.Items[0];
                row["NAME"] = "New name";
                row["ModelCode"] = "New code";
                
                row[FieldNames.DEALER_NET_PRICE] = 0;
                row[FieldNames.DEALER_NET_PRICE_VAT] = 0;
                row[FieldNames.RETAIL_PRICE] = 0;
                row[FieldNames.RETAIL_PRICE_VAT] = 0;

                row[FieldNames.CCM] = 0;
                row[FieldNames.CO2_AVG] = 0;
                row[FieldNames.CO2_MAX] = 0;
                row[FieldNames.CO2_MIN] = 0;

                row[FieldNames.TAX1] = 0;
                row[FieldNames.TAX2] = 0;
                row[FieldNames.TAX3] = 0;

                row[FieldNames.TAX1P] = 0;
                row[FieldNames.TAX2P] = 0;
                row[FieldNames.TAX3P] = 0;

                
                ds.Rows.Add(row);
                this.ScrollToBottom();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void gvModelPricelist_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            var gridView = sender as DataGridView;
            if (null != gridView)
            {
                foreach (DataGridViewRow r in gridView.Rows)
                {
                    gridView.Rows[r.Index].HeaderCell.Value = (r.Index + 1).ToString();
                }
            }
        }

        private void btnImportOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                var settings = AppConfig.GetImportSettings(ImportConfig.Constants.AM_IMPORT_COMMON);
                var decimalSym = settings[ImportConfig.Format.DECIMAL_SYMBOL].GetDefault(".");
                var digitGroup = settings[ImportConfig.Format.DIGIT_GROUPING_SYMBOL].GetDefault(",");
                var separator = settings[ImportConfig.Format.CSV_SEPARATOR].GetDefault(",");

                OpenFileDialog openFileDialogCSV = new OpenFileDialog();

                openFileDialogCSV.InitialDirectory = Application.ExecutablePath.ToString();
                openFileDialogCSV.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*";
                openFileDialogCSV.FilterIndex = 1;
                openFileDialogCSV.RestoreDirectory = true;

                if (openFileDialogCSV.ShowDialog() == DialogResult.OK)
                {
                    List<string[]> listParse = DBAccess.parseCSV(openFileDialogCSV.FileName, separator);
                    // listParse.RemoveAt(0);
                    this.ImportModelPricelist(listParse);
                    //this.btnSaveOptionPricelist_Click(null, null);
                    //this.ImportFromCSV(openFileDialogCSV.FileName);
                    //this.btnSearchOptionPricelist_Click(null, null);
                    MessageBox.Show("Import complete");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Eror", MessageBoxButtons.OK,
                   MessageBoxIcon.Error);
            }
        }

        private void btnExportOptionPricelist_Click(object sender, EventArgs e)
        {
            try
            {
                var pricelistDate = DateTime.Today;
                if (this.cboPricelistDateOptionPricelist.SelectedItem != null)
                {
                    pricelistDate = (DateTime)this.cboPricelistDateOptionPricelist.SelectedItem;
                }
                //MessageBox.Show(pricelistDate.ToString("yyyy.MM.dd"));
                //get all of model
                string modelOid = "";
                DataTable model = this.cboModel.DataSource as DataTable;
                foreach (DataRow row in model.Rows) // Loop over the rows.
                {

                    modelOid += row.ItemArray[0] + ",";
                }
                modelOid = modelOid.Remove(modelOid.LastIndexOf(","));
                //  MessageBox.Show(modelOid);

                var makeOid = (int)(this.cboMakes.SelectedItem as DataRowView)[FieldNames.OID];
                var makeName = (this.cboMakes.SelectedItem as DataRowView)[FieldNames.NAME] as string;
                //var modelOid = (int)(this.cboModel.SelectedItem as DataRowView)[FieldNames.OID];
                var modelYearOid = (int)(this.cboModelYear.SelectedItem as DataRowView)[FieldNames.OID];
                //  var modelYearName = (this.cboModelYear.SelectedItem as DataRowView)[FieldNames.NAME] as string;

                SaveFileDialog saveFileDialogCSV = new SaveFileDialog();
                saveFileDialogCSV.InitialDirectory = Application.ExecutablePath.ToString();

                saveFileDialogCSV.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*";
                saveFileDialogCSV.FilterIndex = 1;
                saveFileDialogCSV.RestoreDirectory = true;
                saveFileDialogCSV.FileName = string.Format("modelPricelist.{0}.{1}.csv", pricelistDate.ToString("yyyy-MM-dd"), makeName);
                //    (string makeOid, string modelOid, string modelYearOid,
                //string countryOid, string currencyOid, string pricelistDate)





                if (saveFileDialogCSV.ShowDialog() == DialogResult.OK)
                {
                    // Runs the export operation if the given filenam is valid.
                    this.exportToCSVfile(saveFileDialogCSV.FileName, makeOid.ToString(), modelOid, modelYearOid.ToString(), "", "", pricelistDate.ToString("yyyy.MM.dd"));
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message, "Eror", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
        }

        #region Functions
        private void ImportModelPricelist(List<string[]> listData)
        {
            var importMap = new Hashtable();
            if (listData.Count > 0)
            {
                var importSettings = AppConfig.GetImportSettings(AppConfig.Constants.AM_IMPORT_MODEL_PRICELIST);

                foreach (var item in importSettings.Keys)
                {
                    var index = int.Parse(importSettings[item as string]);
                    if (index >= listData[0].Length)
                    {
                        var msg = string.Format("Import settings Error: Key {0} index({1}) >= file columns({2})", item, index, listData[0].Length);
                        throw new Exception(msg);
                    }
                    importMap[item] = index;
                }
            }
            else
            {
                throw new Exception("No data to import!");
            }

            var settings = AppConfig.GetImportSettings(ImportConfig.Constants.AM_IMPORT_COMMON);
            var decimalSym = settings[ImportConfig.Format.DECIMAL_SYMBOL].GetDefault(".");
            var digitGroup = settings[ImportConfig.Format.DIGIT_GROUPING_SYMBOL].GetDefault(",");
            var separator = settings[ImportConfig.Format.CSV_SEPARATOR].GetDefault(",");
            var format = new NumberFormatInfo() { NumberDecimalSeparator = decimalSym, NumberGroupSeparator = digitGroup, PercentDecimalDigits = 2 };

            // Default value
            //  var makeOid = (this.cboMakes.SelectedItem as DataRowView)[FieldNames.OID].ToString();
            // var modelOid = (int)(this.cboModel.SelectedItem as DataRowView)[FieldNames.OID];
            var modelYearOid = -1;
            if (this.cboModelYear.SelectedIndex != -1)
                modelYearOid = (int)(this.cboModelYear.SelectedItem as DataRowView)[FieldNames.OID];
            var countryOid = (int)(this.cboCountry.SelectedItem as DataRowView)[FieldNames.OID];
            var currencyOid = (int)(this.cboCurrency.SelectedItem as DataRowView)[FieldNames.OID];
            var pricelistDate = DateTime.Today;
            if (this.cboPricelistDateOptionPricelist.SelectedItem != null)
            {
                pricelistDate = (DateTime)this.cboPricelistDateOptionPricelist.SelectedItem;
            }


            //read config



            var indexMake = (int)importMap[ImportConfig.ModelPricelist.MAKE];
            //var indexModel = (int)importMap[ImportConfig.ModelPricelist.MODEL];
            var indexModelYear = (int)importMap[ImportConfig.ModelPricelist.MODEL_YEAR];
            var indexModelEdition = (int)importMap[ImportConfig.ModelPricelist.MODEL_EDITION];//editioncode
            var indexModelCode = (int)importMap[ImportConfig.ModelPricelist.MODEL_CODE];
            var indexModelName = (int)importMap[ImportConfig.ModelPricelist.MODEL_NAME];
            var indexSubModelName = (int)importMap[ImportConfig.ModelPricelist.SUBMODEL_NAME];

            var indexCCM = (int)importMap[ImportConfig.ModelPricelist.CCM];
            var indexEngine = (int)importMap[ImportConfig.ModelPricelist.ENGINE];
            var indexKW = (int)importMap[ImportConfig.ModelPricelist.KW];
            var indexGear = (int)importMap[ImportConfig.ModelPricelist.GEAR];
            var indexCO2Min = (int)importMap[ImportConfig.ModelPricelist.CO2_MIN];
            var indexCO2Max = (int)importMap[ImportConfig.ModelPricelist.CO2_MAX];
            var indexCO2Avg = (int)importMap[ImportConfig.ModelPricelist.CO2_AVG];
            var indexEmission = (int)importMap[ImportConfig.ModelPricelist.EMISSION];
            var indexFuel = (int)importMap[ImportConfig.ModelPricelist.FUEL];
            var indexTax1p = (int)importMap[ImportConfig.ModelPricelist.TAX_1P];
            var indexTax2p = (int)importMap[ImportConfig.ModelPricelist.TAX_2P];
            var indexTax3p = (int)importMap[ImportConfig.ModelPricelist.TAX_3P];
            var indexTax1 = (int)importMap[ImportConfig.ModelPricelist.TAX_1];
            var indexTax2 = (int)importMap[ImportConfig.ModelPricelist.TAX_2];
            var indexTax3 = (int)importMap[ImportConfig.ModelPricelist.TAX_3];

            var indexDealerNet0 = (int)importMap[ImportConfig.ModelPricelist.DEALER_NET0];
            var indexDealerNet = (int)importMap[ImportConfig.ModelPricelist.DEALER_NET];
            var indexRetail = (int)importMap[ImportConfig.ModelPricelist.RETAIL];
            var indexRetail0 = (int)importMap[ImportConfig.ModelPricelist.RETAIL_0];


            // cached database
            var mapModelYear = new Hashtable();
            var mapModel = new Hashtable();
            var mapModelEdition = new Hashtable();
            var mapSubModelOid = new Hashtable();
            var mapSubModelPriceOid = new Hashtable();
            var mapOptionLanguage = new Hashtable();

           // listData.RemoveAt(0);
            var listCreateSubModelPrice = new List<SubModelPrice>();
            var listUpdateSubModelPrice = new List<SubModelPrice>();

            // VAT
            decimal vat = decimal.Parse(tax);

            // start parsing
            foreach (var data in listData)
            {
                if (data.Length > 0)
                {
                    var makeName = data[indexMake];
                    var makeOid = DBAccess.GetOidByName(makeName, "make");
                    //mapMake[makeName] = makeOid;
                    //check make is exited
                    if (makeOid == -1)
                    {
                        continue;
                    }

                    // update model
                    var modelName = (data[indexModelName]);
                    modelName = modelName.Length > 80 ? modelName.Substring(0, 80) : modelName;
                    //var ogkey = string.Format("{0}|{1}", optionCode, optionGroup);
                    //var og = mapOptionGroup[ogkey] as OptionGroup;
                    var mokey = string.Format("{0}", modelName);
                    // VehicleConfigurator.Model.Model modelObj = null;
                    var modelObj = mapModel[mokey] as VehicleConfigurator.Model.Model;

                    if (modelObj == null)
                    {
                        modelObj = DBAccess.GetModelByName(modelName);

                        if (modelObj == null)
                        {
                            //create new model
                            modelObj = new VehicleConfigurator.Model.Model();
                            modelObj.Name = modelName;
                            modelObj.MakeOID = (int)makeOid;
                            modelObj = DBAccess.CreateModel(modelObj);
                        }
                        mapModel.Add(mokey, modelObj);
                    }


                    //update model year
                    var modelYearName = data[indexModelYear];
                    if(modelYearName.Length >20)
                        modelYearName = modelYearName.Substring(0, 20);

                    var mykey = string.Format("{0}|{1}", modelYearName, makeOid.ToString());
                    var modelYear = mapModelYear[mykey] as ModelYear;

                    if (modelYear == null)
                    {
                        modelYear = DBAccess.GetModelYearByName(modelYearName, makeOid.ToString());
                        if (modelYear == null)
                        {
                            //create new model year
                            modelYear = new ModelYear();
                            modelYear.Name = modelYearName;
                            modelYear.MakeOID = makeOid;
                            modelYear = DBAccess.CreateModelYear(modelYear);
                        }
                        mapModelYear.Add(mykey, modelYear);
                    }
                    //update model edition
                    var modelEditionName = data[indexModelEdition];
                    modelEditionName = modelEditionName.Length > 50 ? modelEditionName.Substring(0, 50) : modelEditionName;
                    var mekey = string.Format("{0}|{1}", modelEditionName, modelObj.OID);
                    var modelEdition = mapModelEdition[mekey] as ModelEdition;
                    if (modelEdition == null)
                    {
                        modelEdition = DBAccess.GetModelEditionByCode(modelEditionName, modelObj.OID.ToString());
                        if (modelEdition == null)
                        {
                            //create model edition
                            modelEdition = new ModelEdition();
                            modelEdition.Name = modelEditionName;
                            modelEdition.ModelOID = modelObj.OID;
                            modelEdition = DBAccess.CreateModelEdition(modelEdition);
                        }
                        mapModelEdition.Add(mekey, modelEdition);
                    }




                    // Duplicate data
                    var subModelPrice = new SubModelPrice();
                    // subModel
                    if (indexSubModelName > -1)
                        subModelPrice.Name = data[indexSubModelName];
                    if (indexModelCode > -1)
                        subModelPrice.ModelCode = data[indexModelCode];

                    subModelPrice.ModelOid = modelObj.OID;
                    subModelPrice.ModelYearOid = modelYear.OID;
                    subModelPrice.ModelEditionOid = modelEdition.OID;
                    if (indexCCM > -1)
                        subModelPrice.CylinderCapacity = string.IsNullOrEmpty(data[indexCCM]) ? 0 : int.Parse(data[indexCCM]);
                    if (indexEngine > -1)
                        subModelPrice.EngineType = data[indexEngine];
                    if (indexKW > -1)
                        subModelPrice.PowerKW = string.IsNullOrEmpty(data[indexKW]) ? 0 : int.Parse(data[indexKW]);
                    if (indexGear > -1)
                        subModelPrice.GearBoxType = data[indexGear];
                    if (indexCO2Min > -1)
                        subModelPrice.CO2EmissionMin = string.IsNullOrEmpty(data[indexCO2Min]) ? 0 : decimal.Parse(data[indexCO2Min], format);
                    if (indexCO2Max > -1)
                        subModelPrice.CO2EmissionMax = string.IsNullOrEmpty(data[indexCO2Max]) ? 0 : decimal.Parse(data[indexCO2Max], format);
                    if (indexCO2Avg > -1)
                        subModelPrice.CO2EmissionAvg = string.IsNullOrEmpty(data[indexCO2Avg]) ? 0 : decimal.Parse(data[indexCO2Avg], format);
                    if (indexEmission > -1)
                        subModelPrice.EmissionNorm = data[indexEmission];
                    if (indexFuel > -1)
                        subModelPrice.FuelType = data[indexFuel];


                    // subModelPrice
                    //subModelPrice.OID = null;
                    //subModelPrice.SubModelOid = subModelOid;
                    //subModelPrice.SubModelName = subModelName;
                    subModelPrice.PricelistDate = pricelistDate;
                    subModelPrice.CountryOid = countryOid;
                    //getvat
                    GetVatFromCountry(countryOid.ToString());

                    subModelPrice.CurrencyOid = currencyOid;
                    if (indexTax1 > -1)
                        subModelPrice.TaxAmount1 = string.IsNullOrEmpty(data[indexTax1]) ? 0 : decimal.Parse(data[indexTax1], format);
                    if (indexTax2 > -1)
                        subModelPrice.TaxAmount2 = string.IsNullOrEmpty(data[indexTax2]) ? 0 : decimal.Parse(data[indexTax2], format);
                    if (indexTax3 > -1)
                        subModelPrice.TaxAmount3 = string.IsNullOrEmpty(data[indexTax3]) ? 0 : decimal.Parse(data[indexTax3], format);
                    if (indexTax1p > -1)
                        subModelPrice.TaxPercentage1 = string.IsNullOrEmpty(data[indexTax1p]) ? 0 : decimal.Parse(data[indexTax1p], format);
                    if (indexTax2p > -1)
                        subModelPrice.TaxPercentage2 = string.IsNullOrEmpty(data[indexTax2p]) ? 0 : decimal.Parse(data[indexTax2p], format);
                    if (indexTax3p > -1)
                        subModelPrice.TaxPercentage3 = string.IsNullOrEmpty(data[indexTax2p]) ? 0 : decimal.Parse(data[indexTax2p], format);
                   
                    if (indexDealerNet0 > -1) // net
                        subModelPrice.DealerNetPrice = string.IsNullOrEmpty(data[indexDealerNet0]) ? 0 : decimal.Parse(data[indexDealerNet0], format);
                    if (subModelPrice.DealerNetPrice != 0)
                    {
                        decimal gros = Utils.GrosCalculate(subModelPrice.DealerNetPrice, vat);
                        subModelPrice.DealerNetPriceVAT = gros;
                    }
                    else if (indexDealerNet > -1)
                    {
                        subModelPrice.DealerNetPriceVAT = string.IsNullOrEmpty(data[indexDealerNet]) ? 0 : decimal.Parse(data[indexDealerNet], format);
                        if (indexDealerNet0 < 0 || subModelPrice.DealerNetPrice==0)
                        {
                            decimal net = Utils.NetCalculate(subModelPrice.DealerNetPriceVAT, vat);
                            subModelPrice.DealerNetPrice = net;
                        }
                    }


                    if (indexRetail0 > -1)
                        subModelPrice.RetailPrice = string.IsNullOrEmpty(data[indexRetail0]) ? 0 : decimal.Parse(data[indexRetail0], format);
                    if (subModelPrice.RetailPrice != 0)
                    {
                        decimal gros = Utils.GrosCalculate(subModelPrice.RetailPrice, vat);
                        subModelPrice.RetailPriceVAT = gros;
                    }
                    else if (indexRetail > -1)
                    {
                        subModelPrice.RetailPriceVAT = string.IsNullOrEmpty(data[indexRetail]) ? 0 : decimal.Parse(data[indexRetail], format);
                        if (indexRetail0 < 0 || subModelPrice.RetailPrice == 0)
                        {
                            decimal net = Utils.NetCalculate(subModelPrice.RetailPriceVAT, vat);
                            subModelPrice.RetailPrice = net;
                        }
                    }

                    //  sup model not update
                    //get supmodel in db


                    int subModelOid = -1;
                    subModelOid = DBAccess.GetSubmodelOid(subModelPrice);
                    if (subModelOid == -1)
                    {
                        // create submodel and submodel price
                        var tempOidSupmodel = DBAccess.CreateSubModel(subModelPrice);
                        subModelPrice.SubModelOid = tempOidSupmodel;
                        //add to list
                        listCreateSubModelPrice.Add(subModelPrice);
                        //DBAccess.CreateSubModelPrice(subModelPrice);
                    }
                    else
                    {
                        //submodel is existsed in DB
                        //not update submodel
                        //update submodel price of this supmodel

                        //1st. get submodelprice
                        var temSubModelPriceOid = DBAccess.GetSubModelPriceBySubModelOid(subModelOid, pricelistDate.ToString("yyyy.MM.dd"));
                        //2nd. update this supmodelprice
                        if (temSubModelPriceOid != -1)
                        {
                            listUpdateSubModelPrice.Add(subModelPrice);
                            DBAccess.UpdateSubModelPrice(temSubModelPriceOid, subModelPrice);
                        }
                        else
                        {
                            subModelPrice.SubModelOid = subModelOid;
                            subModelPrice.ModelEditionOid = modelEdition.OID;
                            listCreateSubModelPrice.Add(subModelPrice);
                            //DBAccess.CreateSubModelPrice(subModelPrice);
                        }
                    }
                    /// insert/update bulk here.
                    //  MessageBox.Show(listCreateSubModelPrice.Count.ToString());
                    if (listCreateSubModelPrice.Count > 200)
                    {
                        DBAccess.CreateBulkSubModelPrice(listCreateSubModelPrice);
                        listCreateSubModelPrice.Clear();
                    }

                    //if (listUpdateSubModelPrice.Count > 300)
                    //{
                    //    DBAccess.UpdateBulkSubModelPrice(listUpdateSubModelPrice);
                    //    listUpdateSubModelPrice.Clear();
                    //}

                }
            }//end of foreach
            //   MessageBox.Show(listUpdateSubModelPrice.Count.ToString());

            // insert or update when size of list <300

            if (listCreateSubModelPrice.Count > 0)
            {
                DBAccess.CreateBulkSubModelPrice(listCreateSubModelPrice);
                listCreateSubModelPrice.Clear();
            }
            //if (listUpdateSubModelPrice.Count > 0)
            //{
            //    DBAccess.UpdateBulkSubModelPrice(listUpdateSubModelPrice);
            //    listUpdateSubModelPrice.Clear();
            //}
        }

        private void ImportFromCSV(string filepath)
        {
            List<string[]> listParse = DBAccess.parseCSV(filepath, ",");
            listParse.RemoveAt(0);


            //var index = this.gvModelPricelist.Rows.Add();
            var ds = this.gvModelPricelist.DataSource as DataTable;
            var dsModel = (this.gvModelPricelist.Columns["ModelOID"] as DataGridViewComboBoxColumn).DataSource as DataTable;
            var dsModelEdition = (this.gvModelPricelist.Columns["ModelEditionOID"] as DataGridViewComboBoxColumn).DataSource as DataTable;

            // Default value
            var cboMakeRow = this.cboMakes.SelectedItem as DataRowView;
            var makeOid = cboMakeRow[FieldNames.OID].ToString();
            var modelOid = (int)(this.cboModel.SelectedItem as DataRowView)[FieldNames.OID];
            var modelYearOid = (int)(this.cboModelYear.SelectedItem as DataRowView)[FieldNames.OID];
            var countryOid = (int)(this.cboCountry.SelectedItem as DataRowView)[FieldNames.OID];
            var currencyOid = (int)(this.cboCurrency.SelectedItem as DataRowView)[FieldNames.OID];

            var pricelistDate = DateTime.Now;
            if (this.cboPricelistDateOptionPricelist.SelectedItem != null)
            {
                pricelistDate = (DateTime)this.cboPricelistDateOptionPricelist.SelectedItem;
            }
            var success = false;
            var today = DateTime.Today;
            foreach (string[] row in listParse)
            {
                if (row.Length > 0)
                {
                    var modelEditionName = row[2];
                    var modelEditionOid = DBAccess.GetModelEditionOidByName(modelEditionName);
                    if (modelEditionOid < 0)
                    {
                        MessageBox.Show(modelEditionName + " not exists. This data row will be skipped import.", "Model edition not exists!");
                        continue;
                    }

                    // Duplicate data
                    var subModelPrice = new SubModelPrice();
                    // subModel
                    subModelPrice.Name = row[4];
                    subModelPrice.ModelCode = row[3];
                    subModelPrice.ModelOid = modelOid;
                    subModelPrice.ModelYearOid = modelYearOid;
                    subModelPrice.ModelEditionOid = DBAccess.GetModelEditionOidByName(row[2]);
                    subModelPrice.CylinderCapacity = string.IsNullOrEmpty(row[5]) ? 0 : int.Parse(row[5]);
                    subModelPrice.EngineType = row[6];
                    subModelPrice.PowerKW = string.IsNullOrEmpty(row[7]) ? 0 : int.Parse(row[7]);
                    subModelPrice.GearBoxType = row[8];
                    subModelPrice.CO2EmissionMin = string.IsNullOrEmpty(row[9]) ? 0 : decimal.Parse(row[9]);
                    subModelPrice.CO2EmissionMax = string.IsNullOrEmpty(row[10]) ? 0 : decimal.Parse(row[10]);
                    subModelPrice.CO2EmissionAvg = string.IsNullOrEmpty(row[11]) ? 0 : decimal.Parse(row[11]);
                    subModelPrice.EmissionNorm = row[12];
                    subModelPrice.FuelType = row[13];

                    // subModelPrice
                    //subModelPrice.OID = null;
                    //subModelPrice.SubModelOid = subModelOid;
                    //subModelPrice.SubModelName = subModelName;
                    subModelPrice.PricelistDate = today;
                    subModelPrice.CountryOid = countryOid;
                    subModelPrice.CurrencyOid = currencyOid;
                    subModelPrice.TaxAmount1 = string.IsNullOrEmpty(row[17]) ? 0 : decimal.Parse(row[17]);
                    subModelPrice.TaxAmount2 = string.IsNullOrEmpty(row[18]) ? 0 : decimal.Parse(row[18]);
                    subModelPrice.TaxAmount3 = string.IsNullOrEmpty(row[19]) ? 0 : decimal.Parse(row[19]);
                    subModelPrice.TaxPercentage1 = string.IsNullOrEmpty(row[14]) ? 0 : decimal.Parse(row[14]);
                    subModelPrice.TaxPercentage2 = string.IsNullOrEmpty(row[15]) ? 0 : decimal.Parse(row[15]);
                    subModelPrice.TaxPercentage3 = string.IsNullOrEmpty(row[16]) ? 0 : decimal.Parse(row[16]);
                    subModelPrice.DealerNetPrice = string.IsNullOrEmpty(row[20]) ? 0 : decimal.Parse(row[20]);
                    subModelPrice.DealerNetPriceVAT = string.IsNullOrEmpty(row[22]) ? 0 : decimal.Parse(row[22]);
                    subModelPrice.RetailPrice = string.IsNullOrEmpty(row[23]) ? 0 : decimal.Parse(row[23]);
                    subModelPrice.RetailPriceVAT = string.IsNullOrEmpty(row[21]) ? 0 : decimal.Parse(row[21]);

                    DBAccess.CreateNewSubModelPrice(subModelPrice);
                    success = true;
                }
            }
            if (success && !this.cboPricelistDateOptionPricelist.Items.Contains(today))
            {
                var dateOnly = this.cboPricelistDateOptionPricelist.DataSource as object[];
                var newSource = new object[dateOnly.Length + 1];
                newSource[0] = today;
                Array.Copy(dateOnly, 0, newSource, 1, dateOnly.Length);
                this.cboPricelistDateOptionPricelist.DataSource = newSource;
                this.cboPricelistDateOptionPricelist.SelectedItem = today;
            }

        }

        private void exportToCSVfile(string fileName, string makeOid, string modelOid, string modelYearOid,
            string countryOid, string currencyOid, string pricelistDate)
        {
            try
            {
                //DataTable dt = this.gvModelPricelist.DataSource as DataTable;
                //DBAccess.ModelPriceExportToCSV(this.gvModelPricelist, fileName, this.cbLanguageOptionPricelist.SelectedValue.ToString());
                var exportMap = new Hashtable();

                var exportSettings = AppConfig.GetExportSettings(AppConfig.Constants.AM_IMPORT_MODEL_PRICELIST);
                foreach (var item in exportSettings.Keys)
                {
                    var index = int.Parse(exportSettings[item as string]);
                    exportMap[item] = index;
                }

                //read config
                var indexMake = (int)exportMap[ImportConfig.ModelPricelist.MAKE];
                //var indexModel = (int)importMap[ImportConfig.ModelPricelist.MODEL];
                var indexModelYear = (int)exportMap[ImportConfig.ModelPricelist.MODEL_YEAR];
                var indexModelEdition = (int)exportMap[ImportConfig.ModelPricelist.MODEL_EDITION];
                var indexModelCode = (int)exportMap[ImportConfig.ModelPricelist.MODEL_CODE];
                var indexModelName = (int)exportMap[ImportConfig.ModelPricelist.MODEL_NAME];
                var indexSubModelName = (int)exportMap[ImportConfig.ModelPricelist.SUBMODEL_NAME];

                var indexCCM = (int)exportMap[ImportConfig.ModelPricelist.CCM];
                var indexEngine = (int)exportMap[ImportConfig.ModelPricelist.ENGINE];
                var indexKW = (int)exportMap[ImportConfig.ModelPricelist.KW];
                var indexGear = (int)exportMap[ImportConfig.ModelPricelist.GEAR];
                var indexCO2Min = (int)exportMap[ImportConfig.ModelPricelist.CO2_MIN];
                var indexCO2Max = (int)exportMap[ImportConfig.ModelPricelist.CO2_MAX];
                var indexCO2Avg = (int)exportMap[ImportConfig.ModelPricelist.CO2_AVG];
                var indexEmission = (int)exportMap[ImportConfig.ModelPricelist.EMISSION];
                var indexFuel = (int)exportMap[ImportConfig.ModelPricelist.FUEL];
                var indexTax1p = (int)exportMap[ImportConfig.ModelPricelist.TAX_1P];
                var indexTax2p = (int)exportMap[ImportConfig.ModelPricelist.TAX_2P];
                var indexTax3p = (int)exportMap[ImportConfig.ModelPricelist.TAX_3P];
                var indexTax1 = (int)exportMap[ImportConfig.ModelPricelist.TAX_1];
                var indexTax2 = (int)exportMap[ImportConfig.ModelPricelist.TAX_2];
                var indexTax3 = (int)exportMap[ImportConfig.ModelPricelist.TAX_3];

                var indexDealerNet0 = (int)exportMap[ImportConfig.ModelPricelist.DEALER_NET0];
                var indexDealerNet = (int)exportMap[ImportConfig.ModelPricelist.DEALER_NET];
                var indexRetail = (int)exportMap[ImportConfig.ModelPricelist.RETAIL];
                var indexRetail0 = (int)exportMap[ImportConfig.ModelPricelist.RETAIL_0];

                var listHeaderValid = new Hashtable();
                if (indexMake > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.MAKE, "Make");
                if (indexModelYear > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.MODEL_YEAR, "ModelYearName");
                if (indexModelCode > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.MODEL_CODE, "ModelCode");
                if (indexModelEdition > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.MODEL_EDITION, "EditionCode");
                if (indexModelCode > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.MODEL_NAME, "ModelName");
                if (indexSubModelName > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.SUBMODEL_NAME, "SubmodelName");
                if (indexCCM > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.CCM, "CylinderCapacity");
                if (indexEngine > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.ENGINE, "EngineType");
                if (indexKW > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.KW, "PowerKW");
                if (indexGear > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.GEAR, "GearBoxType");
                if (indexCO2Min > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.CO2_MIN, "CO2EmissionMin");
                if (indexCO2Max > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.CO2_MAX, "CO2EmissionMax");
                if (indexCO2Avg > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.CO2_AVG, "CO2EmissionAvg");
                if (indexEmission > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.EMISSION, "EmissionNorm");
                if (indexFuel > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.FUEL, "FuelType");
                if (indexRetail0 > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.RETAIL_0, "RetailPrice");
                if (indexRetail > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.RETAIL, "RetailPriceVAT");
                if (indexDealerNet0 > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.DEALER_NET0, "DealerNetPrice");
                if (indexDealerNet > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.DEALER_NET, "DealerNetPriceVAT");
                if (indexTax1 > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.TAX_1, "TaxAmount1");
                if (indexTax2 > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.TAX_2, "TaxAmount2");
                if (indexTax3 > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.TAX_3, "TaxAmount3");
                if (indexTax1p > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.TAX_1P, "TaxPercentage1");
                if (indexTax2p > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.TAX_2P, "TaxPercentage2");
                if (indexTax3p > -1)
                    listHeaderValid.Add(ImportConfig.ModelPricelist.TAX_3P, "TaxPercentage3");

                var listHeaderOrder = new List<String>();
                var listIndexSort = exportMap.Cast<DictionaryEntry>().OrderBy(entry => entry.Value).ToList();
                for (int i = 0; i < listIndexSort.Count; i++)
                {
                    var item = listIndexSort[i];
                    var key = item.Key;
                    var value = listHeaderValid[key] as string;
                    if (!string.IsNullOrEmpty(value))
                    {
                        listHeaderOrder.Add(value);
                    }
                }


                var settings = AppConfig.GetExportSettings(ImportConfig.Constants.AM_IMPORT_COMMON);
                var format = settings[ImportConfig.Format.DECIMAL_FORMAT].GetDefault("0.#####");
                var separator = settings[ImportConfig.Format.CSV_SEPARATOR].GetDefault(",");
                DataTable ds = new DataTable();
                ds = DBAccess.GetModelPricelistExport(makeOid, modelOid, modelYearOid,
                countryOid, currencyOid, pricelistDate);

                // Write header
                var writer = new StreamWriter(fileName, false, Encoding.UTF8);
                for (int i = 0; i < listHeaderOrder.Count; i++)
                {
                    var columnName = listHeaderOrder[i];
                    var displayName = columnName;//this.gvModelPricelist.Columns[columnName].HeaderText;
                    writer.Write(displayName + separator);
                }

                foreach (var item in ds.Rows)
                {
                    writer.WriteLine();
                    var dataRow = item as DataRow;
                    for (int i = 0; i < listHeaderOrder.Count; i++)
                    {
                        var columnName = listHeaderOrder[i];
                        var dataValue = dataRow[columnName].ToString();
                        if (dataRow[columnName].IsDecimal())
                        {
                            dataValue = ((decimal)dataRow[columnName]).ToString(format, CultureInfo.InvariantCulture);
                        }
                        //remove bad token
                        dataValue = dataValue.Replace(separator, string.Empty).Replace(Environment.NewLine, string.Empty);
                        writer.Write(dataValue + separator);
                    }
                }
                writer.Close();
                MessageBox.Show("Export successful.\nFileName: " + fileName, "Information",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                //log.Error(ex.Message);
                //throw;
            }
        }

        #endregion

        private void gvModelPricelist_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            //("Error happened " + e.Context.ToString());
           // MessageBox.Show("Error happened " + e.Context.ToString());
        }

        private void cboCountry_SelectedIndexChanged(object sender, EventArgs e)
        {
            //try
            //{
            //   // MessageBox.Show(cboCountry.SelectedValue.ToString());
            //   this.GetVatFromCountry(cboCountry.SelectedValue.ToString());
            //}
            //catch (Exception ex)
            //{

            //   // MessageBox.Show(ex.Message);
            //}
        }

        private void gvModelPricelist_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            // MessageBox.Show(e.RowIndex.ToString());
            var retailNetIndex = gvModelPricelist.Columns[FieldNames.RETAIL_PRICE].Index;
            var retailGrosIndex = gvModelPricelist.Columns[FieldNames.RETAIL_PRICE_VAT].Index;

            var dealerNetIndex = gvModelPricelist.Columns[FieldNames.DEALER_NET_PRICE].Index;
            var dealerGrosIndex = gvModelPricelist.Columns[FieldNames.DEALER_NET_PRICE_VAT].Index;
            DataGridViewRow r = gvModelPricelist.Rows[e.RowIndex];
            var format = new NumberFormatInfo() { NumberDecimalSeparator = ".", NumberGroupSeparator = ",", PercentDecimalDigits = 2 };

            if (retailNetIndex == e.ColumnIndex) // change net =>update gros
            {
                if (r != null)
                {
                    string netStr = r.Cells[e.ColumnIndex].Value.ToString();
                    if (string.IsNullOrEmpty(r.Cells[e.ColumnIndex].Value.ToString()))
                    {
                        // MessageBox.Show("Invalid Format Value");
                        r.Cells[e.ColumnIndex].Value = 0;
                        netStr = r.Cells[e.ColumnIndex].Value.ToString();

                    }

                    netStr = Utils.CommaToDot(netStr);
                    decimal net = decimal.Parse(netStr, format);
                    decimal vat = decimal.Parse(Utils.CommaToDot(tax), format);
                    decimal gros = Utils.GrosCalculate(net, vat);
                    gvModelPricelist.Rows[e.RowIndex].Cells[retailGrosIndex].Value = gros;//.ToString(CultureInfo.InvariantCulture);

                }
            }

            if (dealerNetIndex == e.ColumnIndex) // change net =>update gros
            {
                if (r != null)
                {
                    string netStr = r.Cells[e.ColumnIndex].Value.ToString();
                    if (string.IsNullOrEmpty(r.Cells[e.ColumnIndex].Value.ToString()))
                    {
                        // MessageBox.Show("Invalid Format Value");
                        r.Cells[e.ColumnIndex].Value = 0;
                        netStr = r.Cells[e.ColumnIndex].Value.ToString();

                    }
                    netStr = Utils.CommaToDot(netStr);

                    decimal net = decimal.Parse(netStr, format);
                    decimal vat = decimal.Parse(Utils.CommaToDot(tax), format);
                    decimal gros = Utils.GrosCalculate(net, vat);
                    gvModelPricelist.Rows[e.RowIndex].Cells[dealerGrosIndex].Value = gros;//.ToString(CultureInfo.InvariantCulture);

                }
            }

            if (retailGrosIndex == e.ColumnIndex) // change gros =>update net
            {
                if (r != null)
                {
                    string grosStr = r.Cells[e.ColumnIndex].Value.ToString();
                    if (string.IsNullOrEmpty(r.Cells[e.ColumnIndex].Value.ToString()))
                    {
                        r.Cells[e.ColumnIndex].Value = 0;
                        grosStr = r.Cells[e.ColumnIndex].Value.ToString();
                    }

                    grosStr = Utils.CommaToDot(grosStr);

                    decimal gros = decimal.Parse(grosStr, format);
                    decimal vat = decimal.Parse(Utils.CommaToDot(tax), format);
                    decimal net = Utils.NetCalculate(gros, vat);
                    gvModelPricelist.Rows[e.RowIndex].Cells[retailNetIndex].Value = net;//.ToString(CultureInfo.InvariantCulture);

                }
            }
            if (dealerGrosIndex == e.ColumnIndex) // change gros =>update net
            {
                if (r != null)
                {
                    string grosStr = r.Cells[e.ColumnIndex].Value.ToString();
                    if (string.IsNullOrEmpty(r.Cells[e.ColumnIndex].Value.ToString()))
                    {
                        r.Cells[e.ColumnIndex].Value = 0;
                        grosStr = r.Cells[e.ColumnIndex].Value.ToString();
                    }
                    grosStr = Utils.CommaToDot(grosStr);
                    decimal gros = decimal.Parse(grosStr, format);
                    decimal vat = decimal.Parse(Utils.CommaToDot(tax), format);
                    decimal net = Utils.NetCalculate(gros, vat);
                    gvModelPricelist.Rows[e.RowIndex].Cells[dealerNetIndex].Value = net;//.ToString(CultureInfo.InvariantCulture);

                }
            }



        }

        private void cboMakes_SelectionChangeCommitted(object sender, EventArgs e)
        {
            this.btnSearchOptionPricelist_Click(sender, e);
        }

    }
}
